/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package pe.core.db;

import com.google.gson.Gson;
import com.google.gson.reflect.TypeToken;
import java.awt.Image;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import static java.util.stream.Collectors.mapping;
import javax.imageio.ImageIO;
import org.hibernate.StatelessSession;
import pe.db.MillionDB;

/**
 *
 * @author romulogalindo
 */
public class MillionDBManager {

    StatelessSession session;

    public MillionDBManager() {
//        session = Proitzen.getSessionFactory().openStatelessSession();
        session = null;
    }

    public List getAnuncios() {
        List<AnuncioDto> anuncios = null;
        Connection con = null;
        try {
            con = MillionDB.getSessionFactory().openStatelessSession().connection();
            Statement st = con.createStatement();
            System.out.println("[getAnuncios] select * from pmanunci.pblst_anuncio();");
            ResultSet rpta = st.executeQuery("select * from pmanunci.pblst_anuncio();");
            Gson gson = new Gson();
            while (rpta.next()) {
                anuncios = new Gson().fromJson(rpta.getString(1), new TypeToken<List<AnuncioDto>>() {
                }.getType());
            }
            System.out.println("[MillionDBManager] Imagenes cargadas... [" + anuncios.size() + "]");
        } catch (SQLException ep) {
            System.out.println("[getAnuncios]:" + ep.getMessage());
        } finally {
            try {
                con.close();
            } catch (SQLException ep) {
            }
        }
        return anuncios;
    }

    public BloqueDto getPosicion(int co_bloque) {
        BloqueDto bloque = new BloqueDto();
        Connection con = null;
        try {
            con = MillionDB.getSessionFactory().openStatelessSession().connection();
            Statement st = con.createStatement();
            System.out.println("[getPosicion] select nu_coordx, nu_coordy from pmbloque.tbbloque where co_bloque=" + co_bloque);
            ResultSet rpta = st.executeQuery("select nu_coordx, nu_coordy from pmbloque.tbbloque where co_bloque=" + co_bloque);
            while (rpta.next()) {
                bloque.setNu_coordx(rpta.getShort(1));
                bloque.setNu_coordy(rpta.getShort(2));
            }
        } catch (SQLException ep) {
            System.out.println("[getPosicion]:" + ep.getMessage());
        } finally {
            try {
                con.close();
            } catch (SQLException ep) {
            }
        }
        return bloque;
    }

    public short getPosicion(int nu_coordx, int nu_coordy) {
        short co_bloque = 0;
        Connection con = null;
        try {
            con = MillionDB.getSessionFactory().openStatelessSession().connection();
            Statement st = con.createStatement();
            System.out.println("[getPosicion] select co_bloque from pmbloque.tbbloque where nu_coordx=" + nu_coordx + " and nu_coordy = " + nu_coordy);
            ResultSet rpta = st.executeQuery("select co_bloque from pmbloque.tbbloque where nu_coordx=" + nu_coordx + " and nu_coordy = " + nu_coordy);
            while (rpta.next()) {
                co_bloque = rpta.getShort(1);
            }
        } catch (SQLException ep) {
            co_bloque = 0;
            System.out.println("[getPosicion]:" + ep.getMessage());
        } finally {
            try {
                con.close();
            } catch (SQLException ep) {
            }
        }
        return co_bloque;
    }

    public Image getImage(int co_arcadj) {
        ArcadjDto arcadj = new ArcadjDto();
        Image img = null;
        Connection con = null;
        try {
            con = MillionDB.getSessionFactory().openStatelessSession().connection();
            Statement st = con.createStatement();
            System.out.println("[getImage] select * from arcadj.pbarcadj(" + co_arcadj + ");");
            ResultSet rpta = st.executeQuery("select * from arcadj.pbarcadj(" + co_arcadj + ")");
            while (rpta.next()) {
                arcadj.setId_arcadj(rpta.getInt(1));
                arcadj.setNo_arcadj(rpta.getString(2));
                arcadj.setFe_arcadj(rpta.getDate(3));
            }
            //armando la imagen
            String dir = "/home/romulogalindo/million";
            String ext_file = arcadj.getNo_arcadj().split("\\.(?=[^\\.]+$)")[1];
            String dir_img = dir + "/" + new SimpleDateFormat("yyyy").format(arcadj.getFe_arcadj()) + "/" + new SimpleDateFormat("MM").format(arcadj.getFe_arcadj()) + "/" + new SimpleDateFormat("dd").format(arcadj.getFe_arcadj()) + "/" + arcadj.getId_arcadj() + "." + ext_file;
            System.out.println("buscando imagen en:" + dir_img);
            File img_file = new File(dir_img);
            if (img_file.exists()) {
                img = ImageIO.read(new FileInputStream(img_file));
            }
            return img;
        } catch (Exception ep) {
            System.out.println("[getImage]:" + ep.getMessage());
            return null;
        } finally {
            try {
                con.close();
            } catch (SQLException ep) {
            }
        }
//        return null;
    }

    public ArcadjDto saveImage(String no_arcadj) {
        ArcadjDto arcadj = new ArcadjDto();
        Connection con = null;
        try {
            con = MillionDB.getSessionFactory().openStatelessSession().connection();
            Statement st = con.createStatement();
            System.out.println("[saveImage][sql:" + "select * from arcadj.pbarcadj_ins('" + no_arcadj + "');" + "]");
            ResultSet rpta = st.executeQuery("select * from arcadj.pbarcadj_ins('" + no_arcadj + "');");
            while (rpta.next()) {
                arcadj.setId_arcadj(rpta.getInt(1));
                arcadj.setFe_arcadj(rpta.getDate(2));
            }
        } catch (SQLException ep) {
            System.out.println("[saveImage]:" + ep.getMessage());
            return null;
        } finally {
            try {
                con.close();
            } catch (SQLException ep) {
            }
        }
        return arcadj;
    }

    public boolean saveAnuncio(AnuncioDto anuncio) {
        String pbins_anuncio = "";
        Connection con = null;
        try {
            con = MillionDB.getSessionFactory().openStatelessSession().connection();
            Statement st = con.createStatement();
            System.out.println("[saveAnuncio] select pmanunci.pbins_anuncio('" + anuncio.getNo_anunci() + "', 1::smallint, '" + anuncio.getUr_anunci() + "', '" + anuncio.getDescripcion() + "', " + anuncio.getCo_bloini() + "::smallint, " + anuncio.getCo_blofin() + "::smallint, " + anuncio.getId_arcadj() + ");");
            ResultSet rpta = st.executeQuery("select pmanunci.pbins_anuncio('" + anuncio.getNo_anunci() + "', 1::smallint, '" + anuncio.getUr_anunci() + "', '" + anuncio.getDescripcion() + "', " + anuncio.getCo_bloini() + "::smallint, " + anuncio.getCo_blofin() + "::smallint, " + anuncio.getId_arcadj() + ");");
            while (rpta.next()) {
                pbins_anuncio = rpta.getString(1);
            }

        } catch (SQLException ep) {
            System.out.println("[saveAnuncio]:" + ep.getMessage());
        } finally {
            try {
                con.close();
            } catch (SQLException ep) {
            }
        }
        return pbins_anuncio.contains("true");
    }

    public void close() {
        try {
            session.close();
        } catch (Exception ep) {

        }
    }

}
